﻿
using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;

namespace GPS.Controllers.GPS_BaoCao
{
    public class GPS_DT_DSHVLXController : Controller
    {
        //
        // GET: /GPS_DT_DSHVLX/
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public ActionResult Index(ViewModelGPS_DAOTAO_DSHVLX SearchModel, int? currentPage)
        {
            //var entities = Getdata(SearchModel);
            SearchModel.pDVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            initialCategorySearchAction(SearchModel);

            DataSet ds = getdatatodataset(SearchModel);
            List<RepostModelGPS_DAOTAO_DSHVLX> model = new List<RepostModelGPS_DAOTAO_DSHVLX>();
            model = GPS.COMMON.Helpers.MapDataTableToList<RepostModelGPS_DAOTAO_DSHVLX>(ds.Tables[1]);


            if (SearchModel.ExportWord == "xuất file")
            {
                RepostModelGPS_DAOTAO_DSHVLX _model = new RepostModelGPS_DAOTAO_DSHVLX();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["KH_HANGGPLX_MA"] == null ? "" : ds.Tables[0].Rows[i]["KH_HANGGPLX_MA"].ToString();
                    _model.KH_HANGGPLX_MA = ds.Tables[0].Rows[i]["DVGTVT_SOGP"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_SOGP"].ToString();
                    //_model.DVGTVT_NGAYGP = ds.Tables[0].Rows[i]["DVGTVT_NGAYGP"] == null ? "" : Convert.ToDateTime( ds.Tables[0].Rows[i]["DVGTVT_NGAYGP"]);
                    _model.KH_NGAYKG = ds.Tables[0].Rows[i]["KH_NGAYKG"] == null ? DateTime.Now : DateTime.Parse(ds.Tables[0].Rows[i]["KH_NGAYKG"].ToString());
                    _model.KH_THOIGIANDT = ds.Tables[0].Rows[i]["KH_THOIGIANDT"] == null ? 0 : decimal.Parse(ds.Tables[0].Rows[i]["KH_THOIGIANDT"].ToString());
                }

                _model.list = model;
                byte[] contentFile = null;
                contentFile = GPS.ENTITYS.EXPORTWORD_DHV_HopDong.EXPORT_HOCVIENHD(Server.MapPath("~/Templates/GPS_Word/HopDongDaoTaoLaiXe1.docx"), _model, "DocxGenericReport");
                string nameOutput = "HopDongDaoTaoLaiXe_" + DateTime.Now.ToShortDateString() + ".docx";
                return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
            }
            else
            {

                // ViewBag.TotalRow = entities.Count();
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
            }
            return View(SearchModel);
        }

        [HttpPost]
        public void initialCategorySearchAction(ViewModelGPS_DAOTAO_DSHVLX SearchModel)
        {
            ViewBag.lstDVGTVT = new SelectList(_db.Filter<SDONVIGTVT>(x => x.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");
            ViewBag.lstDKH = new SelectList(_db.Filter<DKH>(x => x.KH_DVGTVT_MA == SearchModel.pDVGTVT_MA), "KH_MA", "KH_TEN");
        }

        public List<RepostModelGPS_DAOTAO_DSHVLX> Getdata(ViewModelGPS_DAOTAO_DSHVLX SearchModel)
        {
            var param = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (SearchModel.pKH_MA == null ? DBNull.Value : (object)SearchModel.pKH_MA), ParameterDirection.Input);
            var param2 = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var param3 = new OracleParameter("pRetHeader", OracleDbType.RefCursor, ParameterDirection.Output);
            var param4 = new OracleParameter("pRetDSHVLX", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<RepostModelGPS_DAOTAO_DSHVLX>("BEGIN GPS_DAOTAO.GPS_DAOTAO_DSHVLX(:pKH_MA,:pKH_LOP_MA,:pDVGTVT_MA,:pRET); end;", param, param2, param3).ToList();
            return entities;
        }

        public DataSet getdatatodataset(ViewModelGPS_DAOTAO_DSHVLX SearchModel)
        {
            DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_DSHVLX"
                                                   , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pDVGTVT_MA) ? DBNull.Value : (object)SearchModel.pDVGTVT_MA, ParameterDirection.Input)
                                                   , new OracleParameter("pHV_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_MA) ? DBNull.Value : (object)SearchModel.pKH_MA, ParameterDirection.Input)
                                                   , new OracleParameter("pRetHeader", OracleDbType.RefCursor, ParameterDirection.Output)
                                                    , new OracleParameter("pRetDSHVLX", OracleDbType.RefCursor, ParameterDirection.Output)
                                                  );
            return ds;
        }

    }
}